﻿'========================================================================
'功能:員工檔資料庫存取物件
'作者:andy
'日期: 2011/2/9
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data


Namespace OAMS.DAO
    Public Class UsersDAO
        Inherits BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub




        ''' <summary>
        ''' 設定語言別到mySqlDB中
        ''' </summary>
        ''' <param name="UID">使用者代號</param>
        ''' <param name="UICulture">語言別</param>
        ''' <remarks> 設定語言別到mySqlDB中</remarks>

        Public Sub SetLanguage(ByVal UID As String, ByVal UICulture As String)

            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID
            sqlParams(1) = New SqlParameter("@UICulture", SqlDbType.NVarChar)
            sqlParams(1).Value = UICulture
            Dim sb As New StringBuilder()
            sb.AppendLine("declare @aa int ; select @aa =count(*) from SC_UICulture where UID=@UID ;if isnull(@aa,0)=0  begin ")
            sb.AppendLine("insert into SC_UICulture(UID,UICulture)values(@UID,@UICulture) end else begin update SC_UICulture  set UICulture=@UICulture where UID=@UID end ")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

            

        End Sub

        Public Sub UpdateUIDefine(ByVal UID As String, ByVal UICulture As String, ByVal Theme As String, ByVal MenuCollapsible As Boolean, ByVal WinTargetBlank As Boolean)

            Dim sqlParams As SqlParameter() = New SqlParameter(4) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID
            sqlParams(1) = New SqlParameter("@UICulture", SqlDbType.NVarChar)
            sqlParams(1).Value = UICulture
            sqlParams(2) = New SqlParameter("@Theme", SqlDbType.NVarChar)
            sqlParams(2).Value = Theme
            sqlParams(3) = New SqlParameter("@MenuCollapsible", SqlDbType.Bit)
            sqlParams(3).Value = MenuCollapsible
            sqlParams(4) = New SqlParameter("@WinTargetBlank", SqlDbType.Bit)
            sqlParams(4).Value = WinTargetBlank
           
            Dim sb As New StringBuilder()
            sb.AppendLine("declare @aa int ; select @aa =count(*) from SC_UICulture where UID=@UID ;if isnull(@aa,0)=0  begin ")
            sb.AppendLine("insert into SC_UICulture(UID,UICulture,Theme,MenuCollapsible,WinTargetBlank)values(@UID,@UICulture,@Theme,@MenuCollapsible,@WinTargetBlank) end ")
            sb.AppendLine("else begin update SC_UICulture  set UICulture=@UICulture ,Theme=@Theme,MenuCollapsible=@MenuCollapsible,WinTargetBlank=@WinTargetBlank where UID=@UID end ")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)



        End Sub

        ''' <summary>
        ''' 取得語言別到mySqlDB中
        ''' </summary>
        ''' <param name="UID">使用者代號</param>
        '''<returns>取得語言別到mySqlDB中</returns>
        ''' <remarks> 取得語言別到mySqlDB中</remarks>
        Public Function GetLanguage(ByVal UID As String) As String

            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID

            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from  SC_UICulture where UID=@UID")

            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            If dt.Rows.Count = 0 Then
                Return "zh-tw"
            Else
                Return dt.Rows(0)("UICulture").ToString
            End If
        End Function

        Public Function GetTheme(ByVal UID As String) As String

            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID
            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from  SC_UICulture where UID=@UID")

            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            If dt.Rows.Count = 0 Then
                Return "WebTheme"
            Else
                If dt.Rows(0)("Theme").ToString.Trim = "" Then
                    Return "WebTheme"
                Else
                    Return dt.Rows(0)("Theme").ToString
                End If

            End If
        End Function

        Public Function GetMenuCollapsible(ByVal UID As String) As Boolean

            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID
            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from  SC_UICulture where UID=@UID")

            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            If dt.Rows.Count = 0 Then
                Return True
            Else
                Return dt.Rows(0)("MenuCollapsible").ToString = "True"
            End If
        End Function


        Public Function GetWinTargetBlank(ByVal UID As String) As Boolean

            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID

            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from  SC_UICulture where UID=@UID")

            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            If dt.Rows.Count = 0 Then
                Return False
            Else
                Return dt.Rows(0)("WinTargetBlank").ToString = "True"
            End If
        End Function


        ''' <summary>
        ''' 檢查某一個工號是否在資料庫中
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>檢查某一個工號是否在資料庫中</returns>
        ''' <remarks>檢查某一個工號是否在資料庫中</remarks>
        Public Function UserIsExist(ByVal UID As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID


            Dim sb As New StringBuilder()

            sb.AppendLine(" select * from Users where emp_no =@UID")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function
        ''' <summary>
        ''' 查詢員工基本資料傳入工號或姓名都可以空白代表全部
        ''' </summary>
        ''' <param name="UIDOrUserName"></param>
        ''' <returns>查詢員工基本資料傳入工號或姓名都可以空白代表全部</returns>
        ''' <remarks>查詢員工基本資料傳入工號或姓名都可以空白代表全部</remarks>
        Public Function GetUsers(ByVal UIDOrUserName As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UIDOrUserName


            Dim sb As New StringBuilder()

            sb.AppendLine(" select * ")
            sb.AppendLine(" from Users where emp_no like '%" + UIDOrUserName + "%' or emp_name like '%" + UIDOrUserName + "%'")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


        Public Function GetEmpData(ByVal emp_no As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@emp_no", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = emp_no


            Dim sb As New StringBuilder()

            sb.AppendLine(" select * ")
            sb.AppendLine(" from Users where emp_no=@emp_no")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function
        ''' <summary>
        ''' 取得使用者姓名
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得使用者姓名</returns>
        ''' <remarks>取得使用者姓名</remarks>
        Public Function GetUserName(ByVal UID As String) As String
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID


            Dim sb As New StringBuilder()

            sb.AppendLine(" select emp_name as UserName from Users  where emp_no =@UID ")
            Try
                Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString()
            Catch ex As Exception
                Return ""
            End Try

        End Function
        '
        Public Function GetURLRights(ByVal URL As String, ByVal QueryString As String, ByVal UserID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("  select distinct A.ProgramID,B.SeqNo,B.DisplayName,A.Name,A.URL ,C.UseRight,A.isMenu ")
            sb.AppendLine(" from Menu A")
            sb.AppendLine(" left join MenuRights B on A.ProgramID=B.ProgramID")
            sb.AppendLine(" left join GroupRights C on C.ProgramID=B.ProgramID and  C.SeqNo=B.SeqNo")
            'sb.AppendLine(" left join GroupMember D on C.GroupID=D.GroupID ")
            sb.AppendLine(" left join GroupID G on C.GroupID in (isnull(G.RootID,G.GroupID),G.GroupID)   ")
            sb.AppendLine(" left join GroupMember D on G.GroupID=D.GroupID ")
            sb.AppendLine(" where ((A.URL='" & URL & "' and A.QueryString=0) or (A.QueryString=1 and A.URL='" & QueryString & "')) ")
            sb.AppendLine("and ( (A.NeedControl=0)   or   ( D.UID='" & UserID & "'  and C.UseRight=1) ) ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
            
        End Function

        Public Function GetUsersLogin(ByVal LoginID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@Loginid", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = LoginID


            Dim sb As New StringBuilder()

            sb.AppendLine(" select *  ")
            sb.AppendLine(" from Users where LOGONID =@LoginID")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        Public Function GetUC_ID(ByVal emp_no As String) As String
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@emp_no", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = emp_no


            Dim sb As New StringBuilder()

            ' sb.AppendLine(" select Company")
            sb.AppendLine(" select UC_ID")
            sb.AppendLine(" from users where emp_no =@emp_no")


            Try
                Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString()
            Catch ex As Exception
                Return ""
            End Try

        End Function

        Public Function GetUserCategory() As DataTable
         

            Dim sb As New StringBuilder()

            sb.AppendLine(" select * ")
            sb.AppendLine(" from   user_category")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

        End Function
        Public Function GetUserCategoryName(ByVal UC_ID As String) As String


            Dim sb As New StringBuilder()

            sb.AppendLine(" select CompanyName  as UC_NAME ")
            sb.AppendLine(" from  WF_Company  where CompanyID='" & UC_ID + "'")
            Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString()).ToString

        End Function

    End Class


End Namespace


